//
// EigaDb.cs
// 
// Responsible for talking to the database.
//
// Author:
//   Radu Tofoleanu <kenjiru.ro@gmail.com>
//
// Copyright (C) 2007-2008 Radu Tofoleanu (http://kenjiru.ro)
//
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
// "Software"), to deal in the Software without restriction, including
// without limitation the rights to use, copy, modify, merge, publish,
// distribute, sublicense, and/or sell copies of the Software, and to
// permit persons to whom the Software is furnished to do so, subject to
// the following conditions:
// 
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
//


using System;
using System.IO;
using System.Data;
using System.Data.OracleClient;
using System.Xml.Serialization;
using Gtk;

namespace EigaLib
{
	public class EigaDb
	{
		// TODO: conexiunea trebuie sa fie privata
		public OracleConnection dbConn = null;
		// setarile pt. conectarea la baza de date
		public DbSettings dbSettings = null;
		
		public EigaDb()
		{
			this.dbSettings = new DbSettings ();
			this.dbSettings.Load ();
		}
		
		public EigaDb (DbSettings dbSettings)
		{
			this.dbSettings = dbSettings;
		}
		
		public bool Connect ()
		{
			// contruim sirul de conectare la DB
			string connectionString =
				"Data Source=//" + dbSettings.HostName + 
				"/" + dbSettings.DbName + ";" +
				"User ID=" + dbSettings.UserName + ";" +
				"Password=" + dbSettings.Password + ";";
			
			dbConn = new OracleConnection (connectionString);
			try {
				dbConn.Open ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: Connect(): Exception: " + ex.Message);
				dbConn.Dispose ();
				dbConn = null;
				return false;
			}
			
			return true;
		}
		
		public void Disconnect ()
		{
			if (dbConn != null) {
				dbConn.Dispose ();
				dbConn.Close ();
				dbConn = null;
			}
		}
		
		public bool IsConnected ()
		{
			return (this.dbConn != null);
		}
		
		// TODO: exceptii ce trebuiesc aruncate:
		// - nu exista conexiune la DB
		// - utilizatorul applicatiei nu s-a putut loga
		public bool Login ()
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			// incercam sa ne autentificam
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.login";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_user", OracleType.VarChar, 50).Value = dbSettings.AppUserName;
			dbcmd.Parameters.Add ("p_password", OracleType.VarChar, 50).Value = dbSettings.AppPassword;
			
			OracleParameter resParam = new OracleParameter ("p_res", OracleType.Number);
			resParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (resParam);
				
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: Login(): Exception: " + ex.Message);
				dbConn.Dispose ();
				dbConn = null;
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			if (resParam.Value == null) {
				Console.WriteLine("EigaDb.cs: Login(): resParam.Value este null!");
				return false;
			}
			
			decimal res = 0;
			try {
				res = (decimal) resParam.Value;
			} catch (Exception ex) {
				Console.WriteLine (ex.Message);
			}
			
			if ( res == 0) {
				dbConn = null;
				return false;
			}
			
			return true;
		}
		
		public ListStore GetAllCountries ()
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore countriesStore = new ListStore (typeof(int), typeof(string), typeof(string));
			
			// populez cu countries
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.countries_select";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read ()) {
					int id = reader.GetInt32 (0);
					string name = reader.GetString (1);
					string desc = reader.GetString(2);
					countriesStore.AppendValues (id, name, desc);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetAllCountries(): Exception: " + ex.Message);
				return null;
			}
			
			return countriesStore;
		}
		
		public ListStore GetAllGenres ()
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore genresStore = new ListStore (typeof(int), typeof(string), typeof(string));

			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.genres_select";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read ()) {
					int id = reader.GetInt32 (0);
					string name = reader.GetString (1);
					string desc = reader.GetString(2);
					genresStore.AppendValues (id, name, desc);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetAllGenres(): Exception: " + ex.Message);
				return null;
			}
			
			return genresStore;
		}
		
		public ListStore GetAllDirectors ()
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore directorsStore = new ListStore (typeof(int), typeof(string));
			
			// populez cu directors
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.people_select_directors";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read ()) {
					int id = reader.GetInt32 (0);
					string name = reader.GetString (1);
					directorsStore.AppendValues (id, name);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetAllDirectors(): Exception: " + ex.Message);
				return null;
			}
			
			return directorsStore;
		}
		
		public ListStore GetAllMovies (int listID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore moviesStore = new ListStore (typeof(int), typeof(string), typeof(int), 
				typeof(string), typeof(string));
			
			// TODO:  sa fac reformatez query-urile
			string[] procArr = new string[4];
			procArr[0] = "eiga.movies_select_all";
			procArr[1] = "eiga.movies_select_own";
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = procArr[listID];
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read()) {
					int id = reader.GetInt32(0);
					string title = reader.GetString(1);
					int date = reader.GetInt32 (2);
					string genre = reader.GetString(3);
					string rating = reader.GetFloat(4).ToString ();
					moviesStore.AppendValues (id, title, date, genre, rating);
				}
			} catch (Exception ex) {
				Console.WriteLine("EigaDb.cs: GetAllMovies(): Exception: " + ex.Message);
				return null;
			}
			
			return moviesStore;
		}
		
		public TreeStore GetAllPeople ()
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			TreeStore peopleStore = new TreeStore (typeof(int), typeof (string));
			
			TreeIter actorsIter = peopleStore.AppendValues(-1, "Actors");
			TreeIter directorsIter = peopleStore.AppendValues(-2, "Directors");
			
			// populez arborele cu actori
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.people_select";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read()) {
					int id = reader.GetInt32 (0);
					string name = reader.GetString (1);
					int function = reader.GetInt32 (2);
					
					if (function == 0)
						peopleStore.AppendValues (actorsIter, id, name);
					else
						peopleStore.AppendValues (directorsIter, id, name);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetAllPeople(): Exception: " + ex.Message);
				return null;
			}
			
			return peopleStore;
		}
		
		public ListStore GetActorsForMovie (int movieID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore actorsStore = new ListStore (typeof(int), typeof(string));
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.movies_actors_select";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
  			dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = movieID;
  			
  			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
  			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read()) {
					int actorID = reader.GetInt32 (0);
					string actorName = reader.GetString (1);
					
					actorsStore.AppendValues (actorID, actorName);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetActorsForMovie(): Exception: " + ex.Message);
				return null;
			}
			
			return actorsStore;
		}
		
		public bool GetMovie (int movieID, out string title, out string tag, out float rating, 
		                       out int date, out int countryID, out int genreID, 
		                       out int directorID, out byte[] buff)
		{
			title = tag = "";
			rating = date = countryID = genreID = directorID = 0;
			buff = null;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.movies_select_movie";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = movieID;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			OracleParameter blobParam = new OracleParameter ("p_img", OracleType.Blob);
			blobParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (blobParam);
				
			OracleDataReader reader;
			try {
				dbcmd.ExecuteNonQuery ();
				reader = (OracleDataReader) cursorParam.Value;
			}  catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetMovie(): Exception: " + ex.Message);
				return false;
			}
			
			// daca nu am citit nimic, iesim
			if (reader.Read () == false)
				return false;
				
			title = reader.GetString(1);
			date = reader.GetInt32(2);
			rating = reader.GetFloat(3);
			tag = reader.GetString(8);
			
			directorID = reader.GetInt32 (4);
			countryID = reader.GetInt32 (5);
			genreID = reader.GetInt32 (6);
			
			if (reader.IsDBNull (9) == false) {
				OracleLob lob = (OracleLob) blobParam.Value;
				
				MemoryStream ms = new MemoryStream ();
				byte[] buffer = new byte[100];

				while (lob.Read (buffer, 0, 100) > 0) {
					ms.Write (buffer, 0, buffer.Length);
				}
				ms.Close ();
				
				buff = ms.GetBuffer();
			} else {
				buff = null;
			}
			
			return true;
		}
		
		public bool GetPerson (int id, out string name, out int functionID, 
		                       out string birthday, out int countryID, out byte[] buff)
		{
			name = birthday = "";
			functionID = countryID = 0;
			buff = null;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			// incarc info despre persoana
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.people_select_person";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = id;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			OracleParameter blobParam = new OracleParameter ("p_img", OracleType.Blob);
			blobParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (blobParam);
			
			OracleDataReader reader;
			try {
				dbcmd.ExecuteNonQuery ();
				reader = (OracleDataReader) cursorParam.Value;
				if (reader.Read()) {
					id = reader.GetInt32(0);
					name = reader.GetString(1);
					functionID = reader.GetInt32(2);
					birthday = reader.GetString(3);
					countryID = reader.GetInt32(4);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetPerson(): Exception: " + ex.Message);
				return false;
			}
			if (reader.IsDBNull (5) == false) {
				OracleLob lob = (OracleLob) blobParam.Value;
				MemoryStream ms = new MemoryStream ();
				buff = new byte[100];

				while (lob.Read (buff, 0, 100) > 0) {
					ms.Write (buff, 0, buff.Length);
				}
				ms.Close ();
				
				buff = ms.GetBuffer();
			}
			
			return true;
		}
		
		public bool EditMovie (int id, string title, string tag, float rating, 
		                       int date, int countryID, int genreID, 
		                       int directorID, byte[] buff)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.Transaction = dbConn.BeginTransaction ();
			dbcmd.CommandText = "eiga.movies_update";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_title", OracleType.VarChar, 60).Value = title;
			dbcmd.Parameters.Add ("p_tag", OracleType.VarChar, 60).Value = tag;
			dbcmd.Parameters.Add ("p_rating", OracleType.Float).Value = rating;
			dbcmd.Parameters.Add ("p_date", OracleType.Int32).Value = date;
			dbcmd.Parameters.Add ("p_country_id", OracleType.Int32).Value = countryID;
			dbcmd.Parameters.Add ("p_genre_id", OracleType.Int32).Value = genreID;
			dbcmd.Parameters.Add ("p_director_id", OracleType.Int32).Value = directorID;
			dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = id;
			
			OracleParameter p9 = new OracleParameter ("p_img", OracleType.Blob);
			p9.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (p9);
			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleLob lob = (OracleLob) p9.Value;
				lob.BeginBatch(OracleLobOpenMode.ReadWrite);
				lob.Write (buff, 0, buff.Length);
				lob.EndBatch ();
				
				dbcmd.Transaction.Commit ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: EditMovie(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool EditPerson (int id, string name, string birthday, int countryID, 
		                       int function, byte[] buff)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.Transaction = dbConn.BeginTransaction ();
			dbcmd.CommandText = "eiga.people_update";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_birthday", OracleType.VarChar, 100).Value = birthday;
			dbcmd.Parameters.Add("p_country_id", OracleType.Int32).Value = countryID;
			dbcmd.Parameters.Add("p_function", OracleType.Int32).Value = function;
			dbcmd.Parameters.Add("p_id", OracleType.Int32).Value = id;

			OracleParameter p6 = new OracleParameter("p_img", OracleType.Blob);
			p6.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p6);
			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleLob lob = (OracleLob) p6.Value;
				lob.BeginBatch(OracleLobOpenMode.ReadWrite);
				lob.Write (buff, 0, buff.Length);
				lob.EndBatch ();
				dbcmd.Transaction.Commit ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: EditPerson(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool EditCountry (int id, string name, string desc)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.countries_update";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_desc", OracleType.VarChar, 100).Value = desc;
			dbcmd.Parameters.Add("p_id", OracleType.Int32).Value = id;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: EditCountry(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool EditGenre (int id, string name, string desc)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.genres_update";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_desc", OracleType.VarChar, 100).Value = desc;
			dbcmd.Parameters.Add("p_id", OracleType.Int32).Value = id;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: EditGenre(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool AddMovie (out int id, string title, string tag, float rating, 
		                      int date, int countryID, int genreID, 
		                      int directorID, byte[] buff)
		{
			id = 0;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.Transaction = dbConn.BeginTransaction ();
			dbcmd.CommandText = "eiga.movies_insert";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_title", OracleType.VarChar, 60).Value = title;
			dbcmd.Parameters.Add ("p_tag", OracleType.VarChar, 60).Value = tag;
			dbcmd.Parameters.Add ("p_rating", OracleType.Float).Value = rating;
			dbcmd.Parameters.Add ("p_date", OracleType.Int32).Value = date;
			dbcmd.Parameters.Add ("p_country_id", OracleType.Int32).Value = countryID;
			dbcmd.Parameters.Add ("p_genre_id", OracleType.Int32).Value = genreID;
			dbcmd.Parameters.Add ("p_director_id", OracleType.Int32).Value = directorID;
			
			OracleParameter p8 = new OracleParameter("p_id", OracleType.Number);
			p8.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p8);
			
			OracleParameter p9 = new OracleParameter("p_img", OracleType.Blob);
			p9.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p9);
			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleLob lob = (OracleLob) p9.Value;
				lob.BeginBatch(OracleLobOpenMode.ReadWrite);
				lob.Write (buff, 0, buff.Length);
				lob.EndBatch ();
				
				dbcmd.Transaction.Commit ();
			} catch (OracleException ex) {
				Console.WriteLine ("EigaDb.cs: AddMovie(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			if (p8 != null) {
				id = (int) (decimal) p8.Value;
			}
			
			return true;
		}
		
		public bool AddPerson (out int id, string name, string birthday, int countryID, 
		                       int function, byte[] buff)
		{
			id = 0;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.Transaction = dbConn.BeginTransaction ();
			dbcmd.CommandText = "eiga.people_insert";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_birthday", OracleType.VarChar, 100).Value = birthday;
			dbcmd.Parameters.Add("p_country_id", OracleType.Int32).Value = countryID;
			dbcmd.Parameters.Add("p_function", OracleType.Int32).Value = function;
			
			OracleParameter p5 = new OracleParameter("p_id", OracleType.Number);
			p5.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p5);
			
			OracleParameter p6 = new OracleParameter("p_img", OracleType.Blob);
			p6.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p6);
			
			try {
				dbcmd.ExecuteNonQuery ();
				
				OracleLob lob = (OracleLob) p6.Value;
				lob.BeginBatch(OracleLobOpenMode.ReadWrite);
				lob.Write (buff, 0, buff.Length);
				lob.EndBatch ();
				dbcmd.Transaction.Commit ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: AddPerson(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			if (p5 != null) {
				id = (int)(decimal) p5.Value;
			}
			
			return true;
		}
		
		public bool AddActorToMovie (int movieID, int actorID, string role)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.movies_actors_insert";
			dbcmd.CommandType = CommandType.StoredProcedure;
			dbcmd.Parameters.Add ("p_movie_id", OracleType.Int32).Value = movieID;
			dbcmd.Parameters.Add ("p_actor_id", OracleType.Int32).Value = actorID;
			dbcmd.Parameters.Add ("p_role", OracleType.VarChar).Value = role;
			
			try {
				dbcmd.ExecuteNonQuery ();
			}  catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: AddActorToMovie(): Exception: " + ex.Message);
				return false;
			}
			
			return true;
		}
		
		public bool AddCountry (out int id, string name, string desc)
		{
			id = 0;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			
			dbcmd.CommandText = "eiga.countries_insert";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_desc", OracleType.VarChar, 100).Value = desc;
			
			OracleParameter p3 = new OracleParameter("p_id", OracleType.Number);
			p3.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p3);
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: AddCountry(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			if (p3 != null) {
				id = (int) (decimal) p3.Value;
			}
			
			return true;
		}
		
		public bool AddGenre (out int id, string name, string desc)
		{
			id = 0;
			
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			
			dbcmd.CommandText = "eiga.genres_insert";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_name", OracleType.VarChar, 60).Value = name;
			dbcmd.Parameters.Add("p_desc", OracleType.VarChar, 100).Value = desc;
			
			OracleParameter p3 = new OracleParameter("p_id", OracleType.Number);
			p3.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add(p3);
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: AddGenre(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			if (p3 != null) {
				id = (int) (decimal) p3.Value;
			}
			
			return true;
		}
		
		public bool DeleteMovie (int movieID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.movies_delete";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_movie_id", OracleType.Int32).Value = movieID;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: DeleteMovie(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool DeletePerson (int personID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.people_delete";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_person_id", OracleType.Int32).Value = personID;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: DeletePerson(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool DeleteCountry (int countryID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.countries_delete";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_country_id", OracleType.Int32).Value = countryID;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: DeleteCountry(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public bool DeleteGenre (int genreID)
		{
			OracleCommand dbcmd = dbConn.CreateCommand ();
			dbcmd.CommandText = "eiga.genres_delete";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add("p_genre_id", OracleType.Int32).Value = genreID;
			
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: DeleteGenre(): Exception: " + ex.Message);
				return false;
			} finally {
				dbcmd.Dispose ();
				dbcmd = null;
			}
			
			return true;
		}
		
		public ListStore GetCast (int movieID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore castStore = new ListStore (typeof(int), typeof(string), typeof(string));
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.movies_select_cast";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_movie_id", OracleType.Int32).Value = movieID;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			try {
				dbcmd.ExecuteNonQuery ();
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read()) {
					int entryID = reader.GetInt32 (0);
					string roleName = reader.GetString (3);
					string actorName = reader.GetString (4);
					castStore.AppendValues (entryID, actorName, roleName);
				}
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: GetCast(): Exception: " + ex.Message);
				return null;
			}
			
			return castStore;
		}
		
		public ListStore GetCastPerson (int functionID, int personID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return null;
			
			ListStore castList = new ListStore (typeof (string), typeof (string));
			
			// incarc lista cu filme la care a participat persoana
			if (functionID == 0) {
				OracleCommand dbcmd = dbConn.CreateCommand ();
				dbcmd.CommandText = "eiga.people_select_movies";
				dbcmd.CommandType = CommandType.StoredProcedure;
				
				dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = personID;
			
				OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
				cursorParam.Direction = ParameterDirection.Output;
				dbcmd.Parameters.Add (cursorParam);

				try {
					dbcmd.ExecuteNonQuery ();
					OracleDataReader reader = (OracleDataReader) cursorParam.Value;
					while (reader.Read()) {
						string title = reader.GetString (0);
						string role = reader.GetString (1);
						castList.AppendValues (title, role);
					}
				} catch (OracleException ex) {
					Console.WriteLine("EigaDb.cs: GetCastPerson(): Exception: " + ex.Message);
					return null;
				}
			} else {
				OracleCommand dbcmd = dbConn.CreateCommand ();
				dbcmd.CommandText = "eiga.people_select_movies2";
				dbcmd.CommandType = CommandType.StoredProcedure;
				
				dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = personID;
				
				OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
				cursorParam.Direction = ParameterDirection.Output;
				dbcmd.Parameters.Add (cursorParam);
				
				try {
					dbcmd.ExecuteNonQuery ();
					OracleDataReader reader = (OracleDataReader) cursorParam.Value;
					while (reader.Read()) {
						string title = reader.GetString (0);
						castList.AppendValues (title, "");
					}
				} catch (OracleException ex) {
					Console.WriteLine("EigaDb.cs: GetCastPerson(): Exception: " + ex.Message);
					return null;
				}
			}
			
			return castList;
		}
		
		public bool RemoveFromCast (int entryID)
		{
			// verificam daca suntem conectati la baza de date
			if (dbConn == null)
				return false;
			
			OracleCommand dbcmd = dbConn.CreateCommand();
			dbcmd.CommandText = "eiga.movies_actors_delete";
			dbcmd.CommandType = CommandType.StoredProcedure;
			dbcmd.Parameters.Add ("p_id", OracleType.Int32).Value = entryID;
				
			try {
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine("EigaDb.cs: RemoveFromCast(): Exception: " + ex.Message);
				return false;
			}
			
			return true;
		}
	}
}
